package il.ac.tau.cvws.scrabblesidekick.server.db.engine;

import il.ac.tau.cvws.scrabblesidekick.server.db.connection.MySQLConnection;
import il.ac.tau.cvws.scrabblesidekick.server.db.dao.DictionariesDao;
import il.ac.tau.cvws.scrabblesidekick.server.db.dao.LetterDao;
import il.ac.tau.cvws.scrabblesidekick.server.db.template.DBQueryTemplate;


import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class MySQLDBEngine implements DBEngine
{
	private DBQueryTemplate querytStringGen = new DBQueryTemplate();
	private MySQLConnection myConnect = new MySQLConnection();

	@Override
	public List<int[]> findWordTemplate(int dictionaryID, List<Integer> wordTemplate)
			throws SQLException
	{
		Connection connection = null;
		Statement statement  = null;
		List<int[]> solutions = new ArrayList<int[]>();

		String queryString = querytStringGen.queryWord(dictionaryID, wordTemplate);

		try
		{
			connection = myConnect.getConnection();

			statement = connection.createStatement();
			ResultSet rs = statement.executeQuery(queryString);

			while(rs.next())
			{
				int[] sol = retrieveSolution(rs, wordTemplate.size());

				solutions.add(sol);
			}
		}
		catch(SQLException e)
		{
			throw e; 
		}
		finally
		{
			try
			{
				if (statement != null)  statement.close();
				if (connection != null)  connection.close();
			}
			catch (SQLException e)
			{
				throw e; 
			}
		}

		return solutions;
	}

	private int[] retrieveSolution(ResultSet rs, int solutionLength) throws SQLException 
	{

		int[] solution = new int[solutionLength];

		for(int i = 0; i < solutionLength; i++)
		{
			solution[i] = rs.getInt(querytStringGen.getColumn(i));
		}

		return solution;
	}

	public boolean isLeft2RightDictionary(int dictionaryId) throws SQLException 
	{
		Connection connection = null;
		Statement statement  = null;
		int leftToRight = 0;
		try
		{
			connection = myConnect.getConnection();

			statement = connection.createStatement();
			ResultSet rs = statement.executeQuery("SELECT *" + " FROM " + DictionariesDao.getName() +
												  " WHERE " + DictionariesDao.DICTIONARY_ID + " = " + dictionaryId );
			while(rs.next())
			{
				leftToRight = rs.getInt(DictionariesDao.IS_LEFT_TO_RIGHT);
			}
		}
		catch(SQLException e)
		{
			throw e; 
		}
		finally
		{
			try
			{
				if (statement != null)  statement.close();
				if (connection != null)  connection.close();
			}
			catch (SQLException e)
			{
				throw e; 
			}
		}

		return (leftToRight == 1);
	}
	
	public Map<Integer,Integer> getDefinedDictionaries() throws SQLException
	{
		Map<Integer,Integer> definedDictionaries = new HashMap<Integer, Integer>();
		
		Connection connection = null;
		Statement statement  = null;
		int dictionaryId = 0;
		int lettersSum = 0;
		try
		{
			connection = myConnect.getConnection();

			statement = connection.createStatement();
			ResultSet rs = statement.executeQuery("SELECT " + LetterDao.DICTIONARY_ID + ", " + "COUNT(" + LetterDao.LETTER_ID  +") AS lettersCount " + 
												   "FROM " + LetterDao.getName() +
												  " GROUP BY " + LetterDao.DICTIONARY_ID);
			while(rs.next())
			{
				dictionaryId = rs.getInt(DictionariesDao.DICTIONARY_ID);
				lettersSum 	 = rs.getInt("lettersCount");
				definedDictionaries.put(dictionaryId, lettersSum);
			}
		}
		catch(SQLException e)
		{
			throw e; 
		}
		finally
		{
			try
			{
				if (statement != null)  statement.close();
				if (connection != null)  connection.close();
			}
			catch (SQLException e)
			{
				throw e; 
			}
		}
		return definedDictionaries;
	}
}
